package com.epam.rentcar.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.naming.NamingException;

import com.epam.rentcar.transferobjects.Car;
import com.epam.rentcar.transferobjects.CarRequest;
import com.epam.rentcar.transferobjects.User;
import com.epam.rentcar.utils.dbconn.ConnEstablisher;
import com.epam.rentcar.utils.dbconn.ConnectionFactory;

public class CarRequestDAOImpl implements CarRequestDAO {

	ConnEstablisher connEstablisher;
	
	public CarRequestDAOImpl(int wichConnection){
		connEstablisher = ConnectionFactory.getConnection(wichConnection);
	}
	
	private Car getCar(int carID) throws SQLException, NamingException {

		Car car = null;

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {
			connection = connEstablisher.getConnection();
			
			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.CARS WHERE ID=?");
			pstmt.setInt(1, carID);
			pstmt.execute();

			resultSet = pstmt.getResultSet();

			if (resultSet.next()) {
				car = new Car(
						resultSet.getInt("id"),
						resultSet.getString("manufacturer"),
						resultSet.getString("model"),
						resultSet.getString("number")
						);
			} 
		} 
		finally {
			connection.close();	
		}
		return car;	
	}
	
	private User getUser(int userID) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		User user = new User();

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.USERS " +
					"WHERE ID=?");
			pstmt.setInt(1, userID);
			pstmt.execute();

			resultSet = pstmt.getResultSet();

			if (resultSet.next()) {
				user.setID(resultSet.getInt("id"));
				user.setLogin(resultSet.getString("login"));
				return user;
			} else {
				return user;
			}
		} finally {
			connection.close();
		} 
	}


	public boolean addRequest(int userID, String name, String passport, int carID, int rentDuration) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("INSERT INTO ADMIN.REQUEST " +
					"(USERID, NAME, PASSPORT, CARID, RENTDURATION) " +
					"VALUES (?, ?, ?, ?, ?)");

			pstmt.setInt(1, userID);
			pstmt.setString(2, name);
			pstmt.setString(3, passport);
			pstmt.setInt(4, carID);
			pstmt.setInt(5, rentDuration);
			pstmt.execute();
			return true;
		} finally {
			connection.close();
		}
	}

	public List<CarRequest> getAllCancelledRequests() throws SQLException, NamingException {

		List<CarRequest> allCancelledRequests = new ArrayList<CarRequest>();

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.REQUEST " +
					"WHERE CANCELLEDBYADMIN=1");

			pstmt.execute();

			resultSet = pstmt.getResultSet();

			while(resultSet.next()){
				allCancelledRequests.add(new CarRequest(
						resultSet.getInt("ID"),
						getUser(resultSet.getInt("USERID")),
						resultSet.getString("NAME"),
						resultSet.getString("PASSPORT"),
						getCar(resultSet.getInt("CARID")),
						resultSet.getInt("RENTDURATION"),
						resultSet.getInt("PAYED"),
						resultSet.getInt("CANCELLEDBYADMIN"),
						resultSet.getInt("APPROVEDBYADMIN"),
						resultSet.getString("CANCELLATIONPURPOSE"),
						resultSet.getString("paymentpurpose")));}
			return allCancelledRequests;
		} finally {	
			connection.close();
		}
	}

	public List<CarRequest> getAllPayedCarRequests() throws SQLException, NamingException {

		List<CarRequest> allPayedCarRequests = new ArrayList<CarRequest>();

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.REQUEST " +
					"WHERE PAYED=1");

			pstmt.execute();

			resultSet = pstmt.getResultSet();

			while(resultSet.next()){
				allPayedCarRequests.add(new CarRequest(
						resultSet.getInt("ID"),
						getUser(resultSet.getInt("USERID")),
						resultSet.getString("NAME"),
						resultSet.getString("PASSPORT"),
						getCar(resultSet.getInt("CARID")),
						resultSet.getInt("RENTDURATION"),
						resultSet.getInt("PAYED"),
						resultSet.getInt("CANCELLEDBYADMIN"),
						resultSet.getInt("APPROVEDBYADMIN"),
						resultSet.getString("CANCELLATIONPURPOSE"),
						resultSet.getString("paymentpurpose")));}
			return allPayedCarRequests;
		} finally {
			connection.close();
		}
	}

	public List<CarRequest> getAllNotPayedCarRequests() throws SQLException, NamingException {

		List<CarRequest> userCarRequests = new ArrayList<CarRequest>();

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.REQUEST " +
					"WHERE APPROVEDBYADMIN=0 AND PAYED=0 AND CANCELLEDBYADMIN=0");

			pstmt.execute();

			resultSet = pstmt.getResultSet();

			while(resultSet.next()){
				userCarRequests.add(new CarRequest(
						resultSet.getInt("ID"),
						getUser(resultSet.getInt("USERID")),
						resultSet.getString("NAME"),
						resultSet.getString("PASSPORT"),
						getCar(resultSet.getInt("CARID")),
						resultSet.getInt("RENTDURATION"),
						resultSet.getInt("PAYED"),
						resultSet.getInt("CANCELLEDBYADMIN"),
						resultSet.getInt("APPROVEDBYADMIN"),
						resultSet.getString("CANCELLATIONPURPOSE"),
						resultSet.getString("paymentpurpose")));}
			return userCarRequests;
		} finally {
			connection.close();		
		}
	}

	public List<CarRequest> getAllRequests() throws SQLException, NamingException {

		List<CarRequest> userCarRequests = new ArrayList<CarRequest>();

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.REQUEST");

			pstmt.execute();

			resultSet = pstmt.getResultSet();

			while(resultSet.next()){
				userCarRequests.add(new CarRequest(
						resultSet.getInt("ID"),
						getUser(resultSet.getInt("USERID")),
						resultSet.getString("NAME"),
						resultSet.getString("PASSPORT"),
						getCar(resultSet.getInt("CARID")),
						resultSet.getInt("RENTDURATION"),
						resultSet.getInt("PAYED"),
						resultSet.getInt("CANCELLEDBYADMIN"),
						resultSet.getInt("APPROVEDBYADMIN"),
						resultSet.getString("CANCELLATIONPURPOSE"),
						resultSet.getString("paymentpurpose")));}
			return userCarRequests;
		} finally {
			connection.close();		
		}
	}

	public boolean cancellRequest(int requestID, String purpose) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"UPDATE ADMIN.REQUEST SET CANCELLEDBYADMIN=1, cancellationpurpose=? " +
					"WHERE ID=?");
			pstmt.setString(1, purpose);
			pstmt.setInt(2, requestID);
			return pstmt.execute(); 
		} finally {
			connection.close();		
		}
	}

	public boolean approveRequest(int requestID) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"UPDATE ADMIN.REQUEST SET APPROVEDBYADMIN=1 " +
					"WHERE ID=?");
			pstmt.setInt(1, requestID);
			return pstmt.execute(); 
		} finally {	
			connection.close();		
		}
	}

	public boolean deleteRequest(int requestID) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"DELETE FROM ADMIN.REQUEST WHERE ID=?");
			pstmt.setInt(1, requestID);
			return pstmt.execute(); 
		} finally {
			connection.close();		
		}
	}

	public List<CarRequest> getAllUserNotPayedCarRequests(int userID) throws SQLException, NamingException {

		List<CarRequest> allNotPayedCarRequests = new ArrayList<CarRequest>();

		Connection connection = null;
		PreparedStatement pstmt = null;
		ResultSet resultSet = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement("SELECT * FROM ADMIN.REQUEST " +
					"WHERE APPROVEDBYADMIN=1 AND PAYED=0 AND CANCELLEDBYADMIN=0 AND USERID=?");
			pstmt.setInt(1, userID);
			pstmt.execute();

			resultSet = pstmt.getResultSet();

			while(resultSet.next()){
				allNotPayedCarRequests.add(new CarRequest(
						resultSet.getInt("ID"),
						getUser(resultSet.getInt("USERID")),
						resultSet.getString("NAME"),
						resultSet.getString("PASSPORT"),
						getCar(resultSet.getInt("CARID")),
						resultSet.getInt("RENTDURATION"),
						resultSet.getInt("PAYED"),
						resultSet.getInt("CANCELLEDBYADMIN"),
						resultSet.getInt("APPROVEDBYADMIN"),
						resultSet.getString("CANCELLATIONPURPOSE"),
						resultSet.getString("paymentpurpose")));
			}

			return allNotPayedCarRequests;

		}finally {
			connection.close();	
		}
	}

	public boolean setPaymentPurpose(int requestID, String purpose) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"UPDATE ADMIN.REQUEST SET paymentpurpose=? " +
					"WHERE ID=?");
			pstmt.setString(1, purpose);
			pstmt.setInt(2, requestID);
			return pstmt.execute(); 
		} finally {
			connection.close();		
		}
	}

	public boolean payRequest(int requestID) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"UPDATE ADMIN.REQUEST SET payed=1 " +
					"WHERE ID=?");
			pstmt.setInt(1, requestID);
			return pstmt.execute(); 
		} finally {
			connection.close();		
		}
	}

	public boolean setRequestNotPayed(int requestID) throws SQLException, NamingException {

		Connection connection = null;
		PreparedStatement pstmt = null;

		try {

			connection = connEstablisher.getConnection();

			pstmt = connection.prepareStatement(
					"UPDATE ADMIN.REQUEST SET payed=0 " +
					"WHERE ID=?");
			pstmt.setInt(1, requestID);
			return pstmt.execute(); 
		} finally {
			connection.close();
		}
	}

}
